USE [ZOVNP]
GO

/****** Object:  StoredProcedure [dbo].[p_Get_Price_Items_for_ProductType_and_Childs]    Script Date: 12/21/2012 21:00:01 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[p_Get_Price_Items_for_ProductType_and_Childs]
	@rootProductType_ID uniqueidentifier
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    with ProductType_CTE(ID, Name, ParentProductType_ID, TypeLevel) as
	(
		select ID, Name, ParentProductType_ID, 0 as TypeLevel
		from ProductType
		where ID = @rootProductType_ID--ParentProductType_ID is null
		union all
		select pt.ID, pt.Name, pt.ParentProductType_ID, TypeLevel + 1
		from ProductType as pt
			inner join ProductType_CTE as pt_cte
			on pt.ParentProductType_ID = pt_cte.ID
	)

	select
		pi.*
	from
		ProductType_CTE pt inner join
		Product p on pt.ID = p.ProductType_ID inner join
		PriceItem pi on p.ID = pi.Product_ID
	order by p.Name, pi.Cost
END

GO

